/****************************************
** 文件名：  sqlite.c
** 作者：    项目2队
** 说明：    数据库实现文件
****************************************/


#include "sqlite.h"

/* 添加用户 */
int do_insert(sqlite3 *db, char *user_name, char *user_passwd, char *user_phone, char *user_address) {
	char sql[128] = {};
	char *errmsg;

	sprintf(sql, "insert into employee values('%s', '%s', '%s', '%s')", user_name, user_passwd, user_phone, user_address);

	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s\n", errmsg);
	} else {
		printf("insert done\n");
	}

	return 0;
}

/* 修改用户密码 */
int do_update(sqlite3 *db, char *name, char *passwd) {
	char sql[128] = {};
	char *errmsg;

	sprintf(sql, "update employee set passwd = '%s' where name = '%s'", passwd, name);

	if(sqlite3_exec(db, sql,  NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s\n", errmsg);
	} else {
		printf("update done\n");
	}

	return 0;
}

/* 删除用户 */
int do_delete(sqlite3 *db, char *name) {
	char sql[128] = {};
	char *errmsg;

	sprintf(sql, "delete from employee where name = '%s'", name);

	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s\n", errmsg);
	} else {
		printf("delete done\n");
	}
	return 0;
}

/* 查看所有用户 */
int do_select_get_table(sqlite3 *db, char **resultp, int ncow, int ncolumn) {
	char sql[128] = {};
	char *errmsg;
	int i = 0;
	int j = 0;

	sprintf(sql, "select * from employee");

	if (sqlite3_get_table(db, sql, &resultp, &ncow, &ncolumn, &errmsg) != SQLITE_OK) {
		printf("%s\n", errmsg);
	} else {
		printf("select done.\n");
	}
	for (j = 0; j < ncolumn; j++) {
		printf("%-8s ", resultp[j]);
	}
	putchar(10);

	int index = ncolumn;
    int m = 0;
	for (i = 0; i < ncow; i++) {
		for (j = 0; j < ncolumn; j++) {
			printf("%-8s ", resultp[index]);
		}
		putchar(10);
	}

	sqlite3_free_table(resultp);

	return 0;
}

/* 查看特定用户 */
int do_select_get_table_unique_user(sqlite3 *db, char *name, char *passwd) {
	char sql[128] = {};
	char *errmsg;
	char **resultp;
	int ncow;
	int ncolumn;
	int i = 0;
	int j = 0;

	sprintf(sql, "select * from employee where name='%s' and passwd='%s'", name, passwd);

	if (sqlite3_get_table(db, sql, &resultp, &ncow, &ncolumn, &errmsg) != SQLITE_OK) {
		printf("%s\n", errmsg);
        return -1;
	} else {
		printf("select done.\n");
	}
	
	sqlite3_free_table(resultp);

	return 0;
}

/* 查看特定用户信息 */
int do_select_get_table_unique_user_information(sqlite3 *db, char *name) {
	char sql[128] = {};
	char *errmsg;
	char **resultp;
	int ncow;
	int ncolumn;
	int i = 0;
	int j = 0;

	sprintf(sql, "select * from employee where name='%s'", name);

	if (sqlite3_get_table(db, sql, &resultp, &ncow, &ncolumn, &errmsg) != SQLITE_OK) {
		printf("%s\n", errmsg);
	} else {
		printf("select done.\n");
        for (j = 0; j < ncolumn; j++) {
		    printf("%-8s ", resultp[j]);
	    }
	    putchar(10);

	    int index = ncolumn;

        // for (i = 0; i < ncow; i++) {
        	for (j = 0; j < ncolumn; j++) {
        		printf("%-8s ", resultp[index++]);
        	}
        	putchar(10);
        // }
        sqlite3_free_table(resultp);
        return 1;
	}
	
	sqlite3_free_table(resultp);

	return 0;
}
